﻿using System;
using System.Linq;
using System.Text;
using Kaidou.Model;
using System.Data;
using System.Data.SqlClient;
using Kaidou.Util;
using System.Collections;
using Kaidou.Model;

namespace Kaidou.Persister
{
    public class MST_Union_BranchPersister : Common
    {
        /**
	    * 指定された値のデータモデルを取得する。
	    * @param unioncode 組合員コード
	    * @return mst_union_branch データモデル
	    */
        public mst_union_branch getDetail(string unioncode,int branch_code)
        {
            LogManager.info("MST_Union_BranchPersister - getDetail");
            mst_union_branch model = null;
            SqlDB db = new SqlDB();
            try
            {
                StringBuilder sql = new StringBuilder();

                sql.Append("select ");
                sql.Append("union_code, ");
                sql.Append("branch_code, ");
                sql.Append("office_name, ");
                sql.Append("office_kana, ");
                sql.Append("office_zip_no, ");
                sql.Append("office_pref_code, ");
                sql.Append("office_city, ");
                sql.Append("office_address, ");
                sql.Append("office_tel, ");
                sql.Append("office_fax, ");
                sql.Append("office_email, ");
                //sql.Append("daihyo_name, ");
                //sql.Append("daihyo_kana, ");
                sql.Append("tanto_name, ");
                sql.Append("tanto_kana, ");
                //sql.Append("trader_reg_no, ");
                sql.Append("user_clss, ");
                sql.Append("password, ");
                sql.Append("cancel_rest_flg, ");
                sql.Append("union_sts, ");
                sql.Append("reg_date, ");
                sql.Append("upd_date, ");
                sql.Append("upd_union_code, ");
                sql.Append("upd_branch_code, ");
                sql.Append("upd_func_id ");
                sql.Append("from mst_union_branch ");
                sql.Append("where ");
                sql.Append("union_code = @union_code ");
                sql.Append(" and branch_code = @branch_code ");



                SqlParameter[] sqlParameter = { new SqlParameter("@union_code", SqlDbType.VarChar, 5), new SqlParameter("@branch_code", SqlDbType.SmallInt) };
                sqlParameter[0].Value = unioncode;
                sqlParameter[1].Value = branch_code;
                SqlDataReader dataReader = db.GetDataCmd(sql.ToString(), sqlParameter);

                if (dataReader.Read())
                {
                    model = new mst_union_branch();

                    mst_union_branchId MID = new mst_union_branchId();
                    model.Id = MID;

                    model.Id.unioncode = (string)dataReader["union_code"];

                    model.Id.branchcode = (short)dataReader["branch_code"];

                    model.officename = (string)dataReader["office_name"];

                    model.officekana = (string)dataReader["office_kana"];

                    model.officezipno = (string)dataReader["office_zip_no"];

                    model.officeprefcode = (short)dataReader["office_pref_code"];

                    model.officecity = (string)dataReader["office_city"];

                    model.officeaddress = (string)dataReader["office_address"];

                    model.officetel = (string)dataReader["office_tel"];

                    model.officefax = (string)dataReader["office_fax"];

                    model.officeemail = (string)dataReader["office_email"];

                    model.tantoname = (string)dataReader["tanto_name"];

                    model.tantokana = (string)dataReader["tanto_kana"];

                    model.userclss = (string)dataReader["user_clss"];

                    model.password = (string)dataReader["password"];

                    model.cancelrestflg = (string)dataReader["cancel_rest_flg"];

                    model.unionsts = (string)dataReader["union_sts"];

                    model.regdate = (DateTime)dataReader["reg_date"];

                    model.upddate = (DateTime)dataReader["upd_date"];

                    model.updunioncode = (string)dataReader["upd_union_code"];

                    model.updbranchcode = (short)dataReader["upd_branch_code"];

                    model.updfuncid = (string)dataReader["upd_func_id"];
                }



            }
            catch (Exception e)
            {
                LogManager.error("MST_Union_BranchPersister - getDetail " + e.Message);
            }
            finally
            {
                db.Close();
            }

            return model;
        }

        /**
	     *本支店マスタを登録する
	     */
        public void insertMasterBranch(mst_union_branch M)
        {
            SqlDB db = new SqlDB();
            try
            {
                LogManager.info("MST_Union_BranchPersister - insertMasterBranch");

                StringBuilder sql = new StringBuilder();
                sql.Append("insert into mst_union_branch ( ");
                sql.Append("union_code, ");
                sql.Append("branch_code, ");
                sql.Append("office_name, ");
                sql.Append("office_kana, ");
                sql.Append("office_zip_no, ");
                sql.Append("office_pref_code, ");
                sql.Append("office_city, ");
                sql.Append("office_address, ");
                sql.Append("office_tel, ");
                sql.Append("office_fax, ");
                sql.Append("office_email, ");
                //sql.Append("daihyo_name, ");
                //sql.Append("daihyo_kana, ");
                sql.Append("tanto_name, ");
                sql.Append("tanto_kana, ");
                //sql.Append("trader_reg_no, ");
                sql.Append("user_clss, ");
                sql.Append("password, ");
                sql.Append("cancel_rest_flg, ");
                sql.Append("union_sts, ");
                sql.Append("reg_date, ");
                sql.Append("upd_date, ");
                sql.Append("upd_union_code, ");
                sql.Append("upd_branch_code, ");
                sql.Append("upd_func_id ");
                sql.Append(") values ( ");
                sql.Append("@union_code, ");
                sql.Append("@branch_code, ");
                sql.Append("@office_name, ");
                sql.Append("@office_kana, ");
                sql.Append("@office_zip_no, ");
                sql.Append("@office_pref_code, ");
                sql.Append("@office_city, ");
                sql.Append("@office_address, ");
                sql.Append("@office_tel, ");
                sql.Append("@office_fax, ");
                sql.Append("@office_email, ");
                //sql.Append("@daihyo_name, ");
                //sql.Append("@daihyo_kana, ");
                sql.Append("@tanto_name, ");
                sql.Append("@tanto_kana, ");
                //sql.Append("@trader_reg_no, ");
                sql.Append("@user_clss, ");
                sql.Append("@password, ");
                sql.Append("@cancel_rest_flg, ");
                sql.Append("@union_sts, ");
                sql.Append("@reg_date, ");
                sql.Append("@upd_date, ");
                sql.Append("@upd_union_code, ");
                sql.Append("@upd_branch_code, ");
                sql.Append("@upd_func_id )");

                LogManager.info("MST_Union_BranchPersister - sql - " + sql.ToString());


                SqlParameter[] sqlParameter = {   
                                                new SqlParameter("@union_code",SqlDbType.VarChar,5),
                                                new SqlParameter("@branch_code",SqlDbType.SmallInt),
                                                new SqlParameter("@office_name",SqlDbType.VarChar,128),
                                                new SqlParameter("@office_kana",SqlDbType.VarChar,256),
                                                new SqlParameter("@office_zip_no",SqlDbType.VarChar,7),
                                                new SqlParameter("@office_pref_code",SqlDbType.SmallInt),
                                                new SqlParameter("@office_city",SqlDbType.VarChar,256),
                                                new SqlParameter("@office_address",SqlDbType.VarChar,256),
                                                new SqlParameter("@office_tel",SqlDbType.VarChar,11),
                                                new SqlParameter("@office_fax",SqlDbType.VarChar,11),
                                                new SqlParameter("@office_email",SqlDbType.VarChar,80),
                                                //new SqlParameter("@daihyo_name",SqlDbType.VarChar,128),
                                                //new SqlParameter("@daihyo_kana",SqlDbType.VarChar,128),
                                                new SqlParameter("@tanto_name",SqlDbType.VarChar,128),
                                                new SqlParameter("@tanto_kana",SqlDbType.VarChar,128),
                                                //new SqlParameter("@trader_reg_no",SqlDbType.VarChar,8),
                                                new SqlParameter("@user_clss",SqlDbType.VarChar,2),
                                                new SqlParameter("@password",SqlDbType.VarChar,10),
                                                new SqlParameter("@cancel_rest_flg",SqlDbType.VarChar,1),
                                                new SqlParameter("@union_sts",SqlDbType.VarChar,2),
                                                new SqlParameter("@reg_date",SqlDbType.DateTime),
                                                new SqlParameter("@upd_date",SqlDbType.DateTime),
                                                new SqlParameter("@upd_union_code",SqlDbType.VarChar,5),
                                                new SqlParameter("@upd_branch_code",SqlDbType.SmallInt),
                                                new SqlParameter("@upd_func_id",SqlDbType.VarChar,20),
                                              };

                sqlParameter[0].Value = M.Id.unioncode;
                sqlParameter[1].Value = M.Id.branchcode;
                sqlParameter[2].Value = M.officename;
                sqlParameter[3].Value = M.officekana;
                sqlParameter[4].Value = M.officezipno;
                sqlParameter[5].Value = M.officeprefcode;
                sqlParameter[6].Value = M.officecity;
                sqlParameter[7].Value = M.officeaddress;
                sqlParameter[8].Value = M.officetel;
                sqlParameter[9].Value = M.officefax;
                sqlParameter[10].Value = M.officeemail;
                //sqlParameter[11].Value = M.DaihyoName;
                //sqlParameter[11].Value = M.DaihyoKana;
                sqlParameter[11].Value = M.tantoname;
                sqlParameter[12].Value = M.tantokana;
                //sqlParameter[14].Value = M.TraderRegNo;
                sqlParameter[13].Value = M.userclss;
                sqlParameter[14].Value = M.password;
                sqlParameter[15].Value = M.cancelrestflg;
                sqlParameter[16].Value = M.unionsts;
                sqlParameter[17].Value = M.regdate;
                sqlParameter[18].Value = M.upddate;
                sqlParameter[19].Value = M.updunioncode;
                sqlParameter[20].Value = M.updbranchcode;
                sqlParameter[21].Value = M.updfuncid;

                db.ExecuteCmd(sql.ToString(), sqlParameter);


            }
            catch (Exception e)
            {
                LogManager.error("MST_Union_BranchPersister - insertMasterBranch " + e.Message);
            }
            finally
            {
                db.Close();
            }

        }

        /**
        * 指定*本支店マスタ取得する。
        * @param M *本支店マスタ
        * @param pageNo ペ—ジ
        * @param pageSize
        * @return Pager
        */

        public Pager search(BranchSearchModel searchModel, int pageNo, int pageSize)
        {
            LogManager.info("MST_Union_BranchPersister - search");
            ArrayList allresult = new ArrayList();

            String sql =
                "select "
                    + "s.union_code, "
                    + "s.branch_code, "
                    + "s.office_name as branch_name, "
                    + "s.office_kana as branch_kana, "
                    + "s.office_pref_code, "
                    + "s.office_city, "
                    + "k.office_name, "
                    + "k.office_kana "
                    + "from mst_union_branch as s, mst_union as k "
                    + "where s.union_sts != '" + StringUtil.STATUS_DELETE + "'"
                    + " and s.union_code = k.union_code ";

            if (searchModel.Union_code!=null)
            {
                sql += " and s.union_code = '"+searchModel.Union_code+"' ";
            }
            if (searchModel.Branch_code >= 0)
            {
                sql += " and s.branch_code = "+searchModel.Branch_code;
            }
            if (searchModel.Office_name!=null)
            {
                sql += " and k.office_name like '%" + searchModel.Office_name + "%'";
            }
            if (searchModel.Office_kana!=null)
            {
                sql += " and k.office_kana like '%" + searchModel.Office_kana + "%'";
            }
            if (searchModel.Branch_name!=null)
            {
                sql += " and s.office_name like '%" + searchModel.Branch_name + "%'";
            }
            if (searchModel.Branch_kana!=null)
            {
                sql += " and s.office_kana like '%" + searchModel.Branch_kana + "%'";
            }
            if (searchModel.Office_pref_code > 0)
            {
                sql += " and s.office_pref_code =  " + searchModel.Office_pref_code;
            }
            sql += " order by k.office_kana asc , s.office_kana asc ";

            LogManager.info("MST_Union_BranchPersister - search - sql :"+sql);

           
            if (pageNo == 0)
            {
                pageNo = 1;
            }

            SqlDB db = new SqlDB();
            try
            {

                SqlParameter[] sqlParameter = { };



                SqlDataReader dataReader = db.GetDataCmd(sql.ToString(), sqlParameter);

                while (dataReader.Read())
                {
                    BranchSearchModel model = new BranchSearchModel();
                    model.Union_code = (string)dataReader["union_code"];
                    model.Branch_code = (short)dataReader["branch_code"];
                    model.Office_name = (string)dataReader["office_name"];
                    model.Office_kana = (string)dataReader["office_kana"];
                    model.Office_pref_code = (short)dataReader["office_pref_code"];
                    model.Office_city = (string)dataReader["office_city"];
                    model.Branch_name = (string)dataReader["branch_name"];
                    model.Branch_kana = (string)dataReader["branch_kana"];
                    allresult.Add(model);
                }



            }
            catch (Exception exx)
            {
                LogManager.error("MST_Union_BranchPersister - search " + exx.Message);
            }
            finally
            {
                db.Close();
            }

            Pager pager = new Pager();
            pager.PageNo = pageNo;
            pager.PageSize = pageSize;
            pager.Total = allresult.Count;
            pager.Pages = pager.Total % pageSize == 0 ? pager.Total / pageSize : pager.Total / pageSize + 1;
            ArrayList result = new ArrayList();
            object[] bsm = allresult.ToArray();
            for (int i = (pageNo - 1) * pageSize; i < ((((pageNo - 1) * pageSize + pageSize)) >= bsm.Length ? bsm.Length : ((pageNo - 1) * pageSize + pageSize)); i++)
            {
                result.Add((BranchSearchModel)bsm[i]);
                
            }
            pager.List = result;

            return pager;
        }

        /**
        * 本支店マスタを更新する
        * @param M データモデル
        */
        public void updateBranch(mst_union_branch M)
        {
            LogManager.info("MST_Union_BranchPersister - updateBranch");
            SqlDB db = new SqlDB();
            try
            {
                String sql =
            "update mst_union_branch set "
                + "office_name = @office_name, "
                + "office_kana = @office_kana, "
                + "office_zip_no = @office_zip_no, "
                + "office_pref_code = @office_pref_code, "
                + "office_city = @office_city, "
                + "office_address = @office_address, "
                + "office_tel = @office_tel, "
                + "office_fax = @office_fax, "
                + "office_email = @office_email, "
                    //+ "daihyo_name = @daihyo_name, "
                    //+ "daihyo_kana = @daihyo_kana, "
                + "tanto_name = @tanto_name, "
                + "tanto_kana = @tanto_kana, "
                    //+ "trader_reg_no = @trader_reg_no, "
                + "user_clss = @user_clss, "
                + "password = @password, "
                + "cancel_rest_flg = @cancel_rest_flg, "
                + "union_sts = @union_sts, "
                + "reg_date = @reg_date, "
                + "upd_date = @upd_date, "
                + "upd_union_code = @upd_union_code, "
                + "upd_branch_code = @upd_branch_code, "
                + "upd_func_id = @upd_func_id "
                + "where "
                + "union_code = @union_code and branch_code = @branch_code";


                SqlParameter[] sqlParameter = {   
                                                new SqlParameter("@union_code",SqlDbType.VarChar,5),
                                                new SqlParameter("@branch_code",SqlDbType.SmallInt),
                                                new SqlParameter("@office_name",SqlDbType.VarChar,128),
                                                new SqlParameter("@office_kana",SqlDbType.VarChar,256),
                                                new SqlParameter("@office_zip_no",SqlDbType.VarChar,7),
                                                new SqlParameter("@office_pref_code",SqlDbType.SmallInt),
                                                new SqlParameter("@office_city",SqlDbType.VarChar,256),
                                                new SqlParameter("@office_address",SqlDbType.VarChar,256),
                                                new SqlParameter("@office_tel",SqlDbType.VarChar,11),
                                                new SqlParameter("@office_fax",SqlDbType.VarChar,11),
                                                new SqlParameter("@office_email",SqlDbType.VarChar,80),
                                                //new SqlParameter("@daihyo_name",SqlDbType.VarChar,128),
                                                //new SqlParameter("@daihyo_kana",SqlDbType.VarChar,128),
                                                new SqlParameter("@tanto_name",SqlDbType.VarChar,128),
                                                new SqlParameter("@tanto_kana",SqlDbType.VarChar,128),
                                                //new SqlParameter("@trader_reg_no",SqlDbType.VarChar,8),
                                                new SqlParameter("@user_clss",SqlDbType.VarChar,2),
                                                new SqlParameter("@password",SqlDbType.VarChar,10),
                                                new SqlParameter("@cancel_rest_flg",SqlDbType.VarChar,1),
                                                new SqlParameter("@union_sts",SqlDbType.VarChar,2),
                                                new SqlParameter("@reg_date",SqlDbType.DateTime),
                                                new SqlParameter("@upd_date",SqlDbType.DateTime),
                                                new SqlParameter("@upd_union_code",SqlDbType.VarChar,5),
                                                new SqlParameter("@upd_branch_code",SqlDbType.SmallInt),
                                                new SqlParameter("@upd_func_id",SqlDbType.VarChar,20),
                                              };

                sqlParameter[0].Value = M.Id.unioncode;
                sqlParameter[1].Value = M.Id.branchcode;
                sqlParameter[2].Value = M.officename;
                sqlParameter[3].Value = M.officekana;
                sqlParameter[4].Value = M.officezipno;
                sqlParameter[5].Value = M.officeprefcode;
                sqlParameter[6].Value = M.officecity;
                sqlParameter[7].Value = M.officeaddress;
                sqlParameter[8].Value = M.officetel;
                sqlParameter[9].Value = M.officefax;
                sqlParameter[10].Value = M.officeemail;
                sqlParameter[11].Value = M.tantoname;
                sqlParameter[12].Value = M.tantokana;
                sqlParameter[13].Value = M.userclss;
                sqlParameter[14].Value = M.password;
                sqlParameter[15].Value = M.cancelrestflg;
                sqlParameter[16].Value = M.unionsts;
                sqlParameter[17].Value = M.regdate;
                sqlParameter[18].Value = M.upddate;
                sqlParameter[19].Value = M.updunioncode;
                sqlParameter[20].Value = M.updbranchcode;
                sqlParameter[21].Value = M.updfuncid;

                db.ExecuteCmd(sql, sqlParameter);


            }
            catch (Exception e)
            {
                LogManager.error("MST_Union_BranchPersister - updateBranch " + e.Message);
            }
            finally
            {
                db.Close();
            }
        }

        /**
         *  本支店マスタを削除する
         * @param M データモデル
         */
        public void deleteBranch(mst_union_branch M)
        {
            LogManager.info("MST_Union_BranchPersister - deleteBranch - in ");
            LogManager.info("MST_Union_BranchPersister - deleteBranch - branch_code : " + M.Id.branchcode+" - union_code : "+M.Id.unioncode);
            SqlDB db = new SqlDB();
            try
            {
                String pssql = "update mst_union_branch set " + "union_sts = @union_sts, " + "upd_date = @upd_date, " + "upd_union_code = @upd_union_code, " + "upd_branch_code = @upd_branch_code " + "where " + "union_code = @union_code" + " and branch_code=@branch_code";



                SqlParameter[] sqlParameter = {   new SqlParameter("@union_sts", SqlDbType.VarChar, 2),
                                                  new SqlParameter("@upd_date", SqlDbType.DateTime), 
                                                  new SqlParameter("@upd_union_code", SqlDbType.VarChar, 5),
                                                  new SqlParameter("@upd_branch_code", SqlDbType.SmallInt),
                                                  new SqlParameter("@union_code", SqlDbType.VarChar, 5),
                                                  new SqlParameter("@branch_code", SqlDbType.SmallInt)
                                                 
                                              };

                sqlParameter[0].Value = M.unionsts;
                sqlParameter[1].Value = M.upddate;
                sqlParameter[2].Value = M.updunioncode;
                sqlParameter[3].Value = M.updbranchcode;
                sqlParameter[4].Value = M.Id.unioncode;
                sqlParameter[5].Value = M.Id.branchcode;
                db.ExecuteCmd(pssql, sqlParameter);
                

            }
            catch (Exception e)
            {
                LogManager.error("MST_Union_BranchPersister - deleteBranch " + e.Message);
            }
            finally
            {
                db.Close();
            }

        }
        
        /**
	    * 指定された値のデータモデルを取得する。
	    * @param con コネクション
	    * @param union_code 組合員コード
	    * @return Vector BranchSearchModelのデータモデル
	    */
	    public ArrayList getByUnionCode(string union_code) {

            LogManager.info(" MST_Union_BranchPersister getByUnionCode start ,union_code:"+union_code);
		    ArrayList list = new ArrayList();
            SqlDB db = new SqlDB();
            try
            {
                //検索条件を生成
                string sql = "select * from mst_union_branch " + "where " + "union_sts != @union_sts " + "and union_code = @union_code " + "order by branch_code asc";

                SqlParameter[] sqlParameter = { new SqlParameter("@union_code", SqlDbType.VarChar, 5), new SqlParameter("@union_sts", SqlDbType.VarChar, 2) };
                sqlParameter[0].Value = union_code;
                sqlParameter[1].Value = MstUnion.STATUS_DELETE;

                SqlDataReader dataReader = db.GetDataCmd(sql, sqlParameter);

                while (dataReader.Read())
                {
                    BranchSearchModel model = new BranchSearchModel();
                    model.Union_code = (string)dataReader["union_code"];
                    model.Branch_code = (short)dataReader["branch_code"];
                    model.Branch_name = (string)dataReader["office_name"];

                    list.Add(model);
                }


                LogManager.info(" MST_Union_BranchPersister getByUnionCode end ,union_code:" + union_code);


            }
            catch (Exception me)
            {
                LogManager.error(" MST_Union_BranchPersister getByUnionCode error :" + me.Message + me.StackTrace);
            }
            finally
            {
                db.Close();
            }

            return list;

	    }
    
    }
}
